﻿using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Reflection;

namespace Hsp.Auto.Common.Data
{
    /// <summary>
    /// Sql 数据库脚本缓存
    /// 目前只适用于MS SQL SERVER
    /// </summary>
    /// <typeparam name="T">泛型</typeparam>
    public class SqlCache<T>
    {
        /// <summary>
        /// 插入数据脚本
        /// </summary>
        public static string InsertString = "";

        /// <summary>
        /// 删除数据脚本
        /// </summary>
        public static string DeleteString = null;

        /// <summary>
        /// 更新数据脚本
        /// </summary>
        public static string UpdateString = "";

        /// <summary>
        /// 变更数据状态脚本
        /// </summary>
        public static string ChangeStateString = null;

        /// <summary>
        /// 查询数据脚本
        /// </summary>
        public static string QueryString = "";

        /// <summary>
        /// 表名
        /// </summary>
        public static string TableName = "";

        /// <summary>
        /// 主键
        /// </summary>
        public static string PrimaryKey = "";

        /// <summary>
        /// 静态构造函数
        /// </summary>
        static SqlCache()
        {
            Type type = typeof(T);
            var tableName = type.GetTableName(); // 表名称
            var primaryKey = type.GetPrimaryKey(); // 表主键

            TableName = tableName;
            PrimaryKey = primaryKey;

            IEnumerable<PropertyInfo> properties = type.GetProperties();

            #region 插入/更新字段属性筛选


            var queryFields = ""; // 查询字段集合
            var insertFields = ""; // 插入字段集合
            var insertValues = ""; // 插入值参数集合
            var updateFieldValues = ""; // 更新字段及值参数集合
            List<PropertyInfo> insertProperties = new(); // 插入字段属性筛选
            List<PropertyInfo> updateProperties = new(); // 更新字段属性筛选，含主键

            //if (type.IsDefined(typeof(TableAttribute), false))
            //{
            //    List<string> fields = new List<string>();

            //    foreach (var property in properties)
            //    {
            //        if (property.IsDefined(typeof(ColumnAttribute), true))
            //        {
            //            foreach (ColumnAttribute column in property.GetCustomAttributes(typeof(ColumnAttribute), true))
            //            {
            //                if (column.PrimaryKey && !column.Identity)
            //                {
            //                    insertProperties.Add(property); // 添加非标识字段

            //                }
            //                else if (column.PrimaryKey && column.Identity)
            //                {
            //                    // 不添加标识字段
            //                }
            //                else if (!column.PrimaryKey && column.Insert)
            //                {
            //                    insertProperties.Add(property); // 添加普通字段
            //                }

            //                if (column.Update)
            //                {
            //                    updateProperties.Add(property); // 添加普通字段
            //                }

            //                fields.Add(property.Name);
            //            }

            //        }
            //    }

            //    // 为防止字段名称是关键字，因此要使用[]包裹字段名称
            //    queryFields = string.Join(",", fields.Select(field => '[' + field + ']'));
            //    insertFields = string.Join(",", insertProperties.Select(p => '[' + p.Name + ']'));
            //    insertValues = string.Join(",", insertProperties.Select(p => '@' + p.Name));
            //    updateFieldValues = string.Join(",", updateProperties.Where(a => a.Name != primaryKey).Select(p => "[" + p.Name + "]=@" + p.Name));
            //}
            //else
            //{
            //    queryFields = string.Join(",", properties.Select(p => '[' + p.Name + ']'));
            //    insertFields = string.Join(",", properties.Select(p => '[' + p.Name + ']'));
            //    insertValues = string.Join(",", properties.Select(p => '@' + p.Name));
            //    updateFieldValues = string.Join(",", properties.Where(a => a.Name != primaryKey).Select(p => "[" + p.Name + "]=@" + p.Name));
            //}

            // 要注意判断主键数据类型，目前方式没有处理用户设定主键的方式
            queryFields = string.Join(",", properties.Select(p => $"[{p.GetColumnName()}] AS [{p.Name}]"));
            insertFields = string.Join(",", properties.Where(a => a.Name != primaryKey).Select(p => '[' + p.Name + ']'));
            insertValues = string.Join(",", properties.Where(a => a.Name != primaryKey).Select(p => '@' + p.Name));
            updateFieldValues = string.Join(",", properties.Where(a => a.Name != primaryKey).Select(p => "[" + p.Name + "]=@" + p.Name));

            #endregion

            // 插入数据脚本
            InsertString = $"INSERT INTO [{tableName}] ( {insertFields} ) VALUES ( {insertValues} );";

            // 删除数据脚本
            DeleteString = $"DELETE FROM [{tableName}] WHERE ([{primaryKey}]=@{primaryKey});";

            // 更新数据脚本
            UpdateString = $"UPDATE [{tableName}] SET {updateFieldValues} WHERE ({primaryKey} = @{primaryKey});";

            // 查询数据脚本
            QueryString = $"SELECT {queryFields} FROM [{tableName}] WHERE (1=1)";

            // 变更数据状态脚本
            ChangeStateString = $"UPDATE [{tableName}] SET [State] = @State WHERE ([{primaryKey}]=@{primaryKey});";


        }

        /// <summary>
        /// 获取表名称(迁移到AttributeExtend类中)
        /// </summary>
        /// <param name="type">属性类型</param>
        /// <returns></returns>
        //private static string GetTableName(Type type)
        //{
        //    if (type.IsDefined(typeof(TableAttribute), false))
        //    {
        //        TableAttribute table = (TableAttribute)type.GetCustomAttribute(typeof(TableAttribute), false);
        //        return table.Name;
        //    }
        //    else
        //    {
        //        return type.Name;
        //    }
        //}

    }
}
